package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import controllers.ExceptionsTypes;
import budget.Budget;
import budget.Request;
import budget.Service;

public class BudgetWrapper{
	
	public static ArrayList<Budget> getAllBudget(boolean closeConnection) throws Exception{
		ArrayList<Budget> budgets = new ArrayList<Budget>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".budget ";
	    Statement stmt = DBWrapper.getConnection().createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	   
	    	while (rs.next()) {
	    		long idBudget = rs.getInt("id_budget");
	    		long dataInMillis = rs.getLong("validity_date");
	    		long idRequest = rs.getInt("id_budget_request");
	    		double adj = rs.getDouble("adjustment");
	    		GregorianCalendar date = new GregorianCalendar();
	    		date.setTimeInMillis(dataInMillis);
	    		double total = rs.getDouble("total");
	    		ArrayList<Service> listOfServices = ServiceWrapper.getBudgetServices(idBudget, false);
	    		Budget budget = new Budget(idBudget, adj, total, date, RequestWrapper.getRequest(idRequest, false), listOfServices);
	    		try{
	    			EventContractWrapper.getEventContractByBudget(budget, closeConnection);
	    		}catch (Exception e) {
	    			if(e.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
		    			budgets.add(budget);
					}
	        }
	   stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    return budgets;
	}
	
	
	public static Budget getBudget(long idBudget, boolean closeConnection) throws Exception{
		Budget b = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".budget " +
	    		"WHERE id_budget = "+idBudget;
	    Statement stmt = DBWrapper.getConnection().createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	long dataInMillis = rs.getLong("validity_date");
	        	long idRequest = rs.getInt("id_budget_request");
	        	double adj = rs.getDouble("adjustment");
	        	GregorianCalendar date = new GregorianCalendar();
	        	date.setTimeInMillis(dataInMillis);
	        	double total = rs.getDouble("total");
	        	ArrayList<Service> listOfServices = ServiceWrapper.getBudgetServices(idBudget, false);
	        	b = new Budget(idBudget, adj, total, date, RequestWrapper.getRequest(idRequest, false), listOfServices);
	        }
	    stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    if(b == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return b;
	}

	public static Budget getBudgetByRequest(Request request, boolean closeConnection) throws Exception{
		Budget b = null;
		String query = "SELECT * "  +
		    	"FROM "+ DBWrapper.getDbname() +".budget " +
		    	"WHERE id_budget_request = "+ request.getId();
		
	    Statement stmt = DBWrapper.getConnection().createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	long dataInMillis = rs.getLong("validity_date");
	        	long idBudget = rs.getInt("id_budget");
	        	double adj = rs.getDouble("adjustment");
	        	GregorianCalendar date = new GregorianCalendar();
	        	date.setTimeInMillis(dataInMillis);
	        	double total = rs.getDouble("total");
	        	ArrayList<Service> listOfServices = ServiceWrapper.getBudgetServices(idBudget, false);
	        	b = new Budget(idBudget, adj, total, date, request, listOfServices);
	        	if(rs.next())
	        		throw new Exception(ExceptionsTypes.CORRUPT_DB_STATE.name());
	        }
	     stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
		if(b == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return b;
	}
	
	
	public static void insertBudget(boolean closeConnection, Budget ... budgets) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		for(int i = 0 ; i<budgets.length; i++){
			Budget b = budgets[i];
			//verifico se la richiesta esiste altrimenti eccezione
			try{
				RequestWrapper.getRequest(b.getRequest().getId(), false);
			}catch(Exception ex1){
				if(ex1.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
					throw new Exception(ExceptionsTypes.DIPENDENCE_ERROR.name());
			}
		
			int rows = stmt.executeUpdate(
		        	"INSERT INTO "+ DBWrapper.getDbname() +".budget" +
		        	"(id_budget_request, validity_date, total, adjustment) " +
		            "VALUES(" + b.getRequest().getId() + ", '" + b.getLastDate().getTimeInMillis() +"', "+ b.getTotal() + ", "+b.getAdjustments()+")");
			if(rows == 0)
				throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
			b.setId(getBudgetByRequest(b.getRequest(), false).getId());
		}
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	
	public static void deleteBudget(boolean closeConnection, Budget budget) throws Exception{
	    String query = "DELETE "  +
	    		"FROM "+ DBWrapper.getDbname() +".budget " +
	    		"WHERE id_budget = "+ budget.getId();
	    Statement stmt = DBWrapper.getConnection().createStatement();
	    int rows = stmt.executeUpdate(query);
	    
	    if(rows>1){
	    	throw new SQLException();
	    }else if(rows == 0)
	    	throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	    
	    RequestWrapper.deleteRequest(false, budget.getRequest());
	    stmt.close();
	    if(closeConnection)
	    	DBWrapper.getConnection().close();
	}
	
	public static void modifyBudget(boolean closeConnection, Budget budget) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		//verifico se esiste altrimenti propago l'eccezione
		BudgetWrapper.getBudget(budget.getId(), false);
		ServiceWrapper.insertListOfServices(budget, false);

		int rows = stmt.executeUpdate(
		        "UPDATE "+ DBWrapper.getDbname() +".budget" +
		        " SET validity_date=" + budget.getLastDate().getTimeInMillis() +
		        ", total=" + budget.getTotalCostWhitoutAdjustment() +
		        ", adjustment="+budget.getAdjustments() +
		        " WHERE id_budget=" + budget.getId());
		
		if(rows == 0)
			throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
        if(closeConnection)
        	DBWrapper.getConnection().close();
        
	}
	
	
}
